import com.alibaba.excel.EasyExcel;

import javax.swing.*;
import javax.swing.table.DefaultTableModel;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.Vector;

public class DormitoryManagementSystem extends JFrame {
    private  Connection connection;
    private Statement statement;
    private ResultSet resultSet;
    private DefaultTableModel suSheModel = new DefaultTableModel(new String[]{"主键", "楼号", "房号", "入住人数","状态"}, 0);
    private DefaultTableModel stuModel = new DefaultTableModel(new String[]{"主键", "学号", "姓名", "性别","年龄","毕业"}, 0);
    private DefaultTableModel accidentModel = new DefaultTableModel(new String[]{"主键", "楼号", "房号", "事故描述"}, 0);
    private DefaultTableModel goodModel = new DefaultTableModel(new String[]{"主键", "楼号", "物品名称", "数量"}, 0);
    private JTable suSheTable;
    private JTable stuTable;
    private JTable accidentTable;
    private JTable goodTable;
    private JTextField suShebuildingField ;
    private JTextField suSheRoomField;
    private JTextField stuNoField = new JTextField(10);
    private JTextField stuNameField = new JTextField(10);

    private JTextField accidentBuildingField = new JTextField(10);
    private JTextField accidentRoomField = new JTextField(10);

    private JTextField goodBuildingField = new JTextField(10);
    private JTextField goodNameField = new JTextField(10);

    public DormitoryManagementSystem() {
        JFrame frame = new JFrame("宿舍基本信息管理系统");
        frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        frame.setSize(800, 600);

        JTabbedPane tabbedPane = createTabbedPane();

        frame.getContentPane().add(tabbedPane);
        frame.setVisible(true);
    }

    private JTabbedPane createTabbedPane() {
        JTabbedPane tabbedPane = new JTabbedPane();

        JPanel residentInfoPanel = createSuShePanel();
        tabbedPane.addTab("宿舍基本信息", residentInfoPanel);

        JPanel studentPanel = createStudentPanel();
        tabbedPane.addTab("学生基本信息", studentPanel);

        JPanel accidentPanel = createAccidentPanel();
        tabbedPane.addTab("宿舍事故信息", accidentPanel);

        JPanel goodPanel = createGoodPanel();
        tabbedPane.addTab("宿舍楼物品信息", goodPanel);
        return tabbedPane;
    }

    private JPanel createGoodPanel() {
        // 创建界面
        JPanel searchPanel = new JPanel();
        goodBuildingField = new JTextField(10);
        goodNameField = new JTextField(10);
        JButton searchButton = new JButton("搜索");
        searchButton.addActionListener(new GoodSearchButtonListener());
        searchPanel.add(new JLabel("楼号："));
        searchPanel.add(goodBuildingField);
        searchPanel.add(new JLabel("物品名称："));
        searchPanel.add(goodNameField);
        searchPanel.add(searchButton);

        JPanel tablePanel = new JPanel();
        goodTable = new JTable(goodModel);
        JScrollPane scrollPane = new JScrollPane(goodTable);

        JButton printButton = new JButton("打印");
        printButton.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {
                // 获取导出文件保存路径
                String filePath = "宿舍物品信息导出.xlsx";
                try {
                    // 创建Excel数据列表
                    java.util.List<java.util.List<Object>> dataList = new ArrayList<>();
                    // 添加表格列名
                    java.util.List<Object> columnNameList = new ArrayList<>();
                    for (int i = 0; i < goodModel.getColumnCount(); i++) {
                        columnNameList.add(goodModel.getColumnName(i));
                    }
                    dataList.add(columnNameList);
                    // 添加表格数据
                    for (int i = 0; i < goodModel.getRowCount(); i++) {
                        java.util.List<Object> rowDataList = new ArrayList<>();
                        for (int j = 0; j < goodModel.getColumnCount(); j++) {
                            rowDataList.add(goodModel.getValueAt(i, j));
                        }
                        dataList.add(rowDataList);
                    }
                    // 使用EasyExcel导出数据到Excel文件
                    EasyExcel.write(new FileOutputStream(new File(filePath))).sheet("Sheet1").doWrite(dataList);
                    JOptionPane.showMessageDialog(DormitoryManagementSystem.this, "导出成功！");
                } catch (IOException ex) {
                    ex.printStackTrace();
                    JOptionPane.showMessageDialog(DormitoryManagementSystem.this, "导出失败！");
                }
            }
        });
        searchPanel.add(printButton);
        JPanel buttonPanel = new JPanel();
        JButton modifyButton = new JButton("修改");
        JButton deleteButton = new JButton("删除");
        modifyButton.addActionListener(new GoodModifyButtonListener());
        deleteButton.addActionListener(new GoodDeleteButtonListener());
        buttonPanel.add(modifyButton);
        buttonPanel.add(deleteButton);

        tablePanel.setLayout(new BorderLayout());
        tablePanel.add(searchPanel, BorderLayout.NORTH);
        tablePanel.add(scrollPane, BorderLayout.CENTER);
        tablePanel.add(buttonPanel, BorderLayout.SOUTH);

        // 获取数据库连接
        connection = JdbcConnection.getConnection();

        // 查询所有记录并显示在表格中
        executeQuery("SELECT * FROM accidents",accidentModel);
        return tablePanel;
    }

    private class GoodModifyButtonListener implements ActionListener {
        @Override
        public void actionPerformed(ActionEvent e) {
            int selectedRow = goodTable.getSelectedRow();
            if (selectedRow >= 0) {
                // 获取选中行的id
                long id =  (Long)goodModel.getValueAt(selectedRow, 0) ;
                String no = (String) goodModel.getValueAt(selectedRow, 1);
                String name = (String) goodModel.getValueAt(selectedRow, 2);
                int goodCount = (int) goodModel.getValueAt(selectedRow, 3);
                // 创建修改窗口
                JFrame modifyFrame = new JFrame("修改宿舍物品信息");
                modifyFrame.setLayout(new FlowLayout());
                modifyFrame.setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);
                modifyFrame.setSize(300, 200);

                // 添加修改窗口的组件
                JTextField noField = new JTextField(no, 15);
                JTextField nameField = new JTextField(name, 15);
                JTextField goodCountField = new JTextField(String.valueOf(goodCount), 10);
                JButton confirmButton = new JButton("确认");
                confirmButton.addActionListener(new ActionListener() {
                    @Override
                    public void actionPerformed(ActionEvent e) {
                        try {
                            // 更新数据库中的记录
                            PreparedStatement preparedStatement = connection.prepareStatement("UPDATE goods SET building_no=?, goods_name=?, goods_count=? WHERE id=?");
                            preparedStatement.setString(1, noField.getText());
                            preparedStatement.setString(2, nameField.getText());
                            preparedStatement.setString(3, goodCountField.getText());
                            preparedStatement.setLong(4, id);
                            preparedStatement.executeUpdate();

                            // 更新表格显示
                            executeQuery("SELECT * FROM goods",stuModel);

                            // 关闭修改窗口
                            modifyFrame.dispose();
                        } catch (SQLException ex) {
                            ex.printStackTrace();
                        }
                    }
                });

                modifyFrame.add(new JLabel("楼号："));
                modifyFrame.add(noField);
                modifyFrame.add(new JLabel("物品名称："));
                modifyFrame.add(nameField);
                modifyFrame.add(new JLabel("物品数量："));
                modifyFrame.add(goodCountField);
                modifyFrame.add(confirmButton);

                modifyFrame.setVisible(true);
            }
        }
    }

    private JPanel createAccidentPanel() {
        // 创建界面
        JPanel searchPanel = new JPanel();
        accidentBuildingField = new JTextField(10);
        accidentRoomField = new JTextField(10);
        JButton searchButton = new JButton("搜索");
        searchButton.addActionListener(new AccidentSearchButtonListener());
        searchPanel.add(new JLabel("楼号："));
        searchPanel.add(accidentBuildingField);
        searchPanel.add(new JLabel("房号："));
        searchPanel.add(accidentRoomField);
        searchPanel.add(searchButton);

        JPanel tablePanel = new JPanel();
        accidentTable = new JTable(accidentModel);
        JScrollPane scrollPane = new JScrollPane(accidentTable);


        JButton printButton = new JButton("打印");
        printButton.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {
                // 获取导出文件保存路径
                String filePath = "宿舍事故信息导出.xlsx";
                try {
                    // 创建Excel数据列表
                    java.util.List<java.util.List<Object>> dataList = new ArrayList<>();
                    // 添加表格列名
                    java.util.List<Object> columnNameList = new ArrayList<>();
                    for (int i = 0; i < accidentModel.getColumnCount(); i++) {
                        columnNameList.add(accidentModel.getColumnName(i));
                    }
                    dataList.add(columnNameList);
                    // 添加表格数据
                    for (int i = 0; i < accidentModel.getRowCount(); i++) {
                        java.util.List<Object> rowDataList = new ArrayList<>();
                        for (int j = 0; j < accidentModel.getColumnCount(); j++) {
                            rowDataList.add(accidentModel.getValueAt(i, j));
                        }
                        dataList.add(rowDataList);
                    }
                    // 使用EasyExcel导出数据到Excel文件
                    EasyExcel.write(new FileOutputStream(new File(filePath))).sheet("Sheet1").doWrite(dataList);
                    JOptionPane.showMessageDialog(DormitoryManagementSystem.this, "导出成功！");
                } catch (IOException ex) {
                    ex.printStackTrace();
                    JOptionPane.showMessageDialog(DormitoryManagementSystem.this, "导出失败！");
                }
            }
        });
        searchPanel.add(printButton);
        JPanel buttonPanel = new JPanel();
        JButton modifyButton = new JButton("修改");
        JButton deleteButton = new JButton("删除");
        modifyButton.addActionListener(new AccidentModifyButtonListener());
        deleteButton.addActionListener(new AccidentDeleteButtonListener());
        buttonPanel.add(modifyButton);
        buttonPanel.add(deleteButton);

        tablePanel.setLayout(new BorderLayout());
        tablePanel.add(searchPanel, BorderLayout.NORTH);
        tablePanel.add(scrollPane, BorderLayout.CENTER);
        tablePanel.add(buttonPanel, BorderLayout.SOUTH);

        // 获取数据库连接
        connection = JdbcConnection.getConnection();

        // 查询所有记录并显示在表格中
        executeQuery("SELECT * FROM accidents",accidentModel);
        return tablePanel;
    }

    private JPanel createStudentPanel() {
        // 创建界面
        JPanel searchPanel = new JPanel();
        stuNoField = new JTextField(10);
        stuNameField = new JTextField(10);
        JButton searchButton = new JButton("搜索");
        searchButton.addActionListener(new StudentSearchButtonListener());
        searchPanel.add(new JLabel("学号："));
        searchPanel.add(stuNoField);
        searchPanel.add(new JLabel("姓名："));
        searchPanel.add(stuNameField);
        searchPanel.add(searchButton);

        JPanel tablePanel = new JPanel();
        stuTable = new JTable(stuModel);
        JScrollPane scrollPane = new JScrollPane(stuTable);

        JButton printButton = new JButton("打印");
        printButton.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {
                // 获取导出文件保存路径
                String filePath = "学生基本信息导出.xlsx";
                try {
                    // 创建Excel数据列表
                    java.util.List<java.util.List<Object>> dataList = new ArrayList<>();
                    // 添加表格列名
                    java.util.List<Object> columnNameList = new ArrayList<>();
                    for (int i = 0; i < stuModel.getColumnCount(); i++) {
                        columnNameList.add(stuModel.getColumnName(i));
                    }
                    dataList.add(columnNameList);
                    // 添加表格数据
                    for (int i = 0; i < stuModel.getRowCount(); i++) {
                        java.util.List<Object> rowDataList = new ArrayList<>();
                        for (int j = 0; j < stuModel.getColumnCount(); j++) {
                            rowDataList.add(stuModel.getValueAt(i, j));
                        }
                        dataList.add(rowDataList);
                    }
                    // 使用EasyExcel导出数据到Excel文件
                    EasyExcel.write(new FileOutputStream(new File(filePath))).sheet("Sheet1").doWrite(dataList);
                    JOptionPane.showMessageDialog(DormitoryManagementSystem.this, "导出成功！");
                } catch (IOException ex) {
                    ex.printStackTrace();
                    JOptionPane.showMessageDialog(DormitoryManagementSystem.this, "导出失败！");
                }
            }
        });
        searchPanel.add(printButton);

        JPanel buttonPanel = new JPanel();
        JButton modifyButton = new JButton("修改");
        JButton deleteButton = new JButton("删除");
        JButton addButton = new JButton("增加");
        addButton.addActionListener(new StudentAddButtonListener());
        modifyButton.addActionListener(new StuModifyButtonListener());
        deleteButton.addActionListener(new StuDeleteButtonListener());
        buttonPanel.add(addButton);
        buttonPanel.add(modifyButton);
        buttonPanel.add(deleteButton);


        tablePanel.setLayout(new BorderLayout());
        tablePanel.add(searchPanel, BorderLayout.NORTH);
        tablePanel.add(scrollPane, BorderLayout.CENTER);
        tablePanel.add(buttonPanel, BorderLayout.SOUTH);

        // 获取数据库连接
        connection = JdbcConnection.getConnection();

        // 查询所有记录并显示在表格中
        executeQuery("SELECT * FROM students",stuModel);
        return tablePanel;
    }

    private JPanel createSuShePanel() {
        JPanel searchPanel, tablePanel, buttonPanel;

         JButton searchButton, modifyButton, deleteButton;

        // 创建界面
        searchPanel = new JPanel();
        suShebuildingField = new JTextField(10);
        suSheRoomField = new JTextField(10);
        searchButton = new JButton("搜索");
        searchButton.addActionListener(new SuSheSearchButtonListener());
        searchPanel.add(new JLabel("楼号："));
        searchPanel.add(suShebuildingField);
        searchPanel.add(new JLabel("房间号："));
        searchPanel.add(suSheRoomField);
        searchPanel.add(searchButton);
        //不可查询触发器维护

        JButton printButton = new JButton("打印");
        printButton.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {
                // 获取导出文件保存路径
                String filePath = "output.xlsx";
                try {
                    // 创建Excel数据列表
                    java.util.List<java.util.List<Object>> dataList = new ArrayList<>();
                    // 添加表格列名
                    java.util.List<Object> columnNameList = new ArrayList<>();
                    for (int i = 0; i < suSheModel.getColumnCount(); i++) {
                        columnNameList.add(suSheModel.getColumnName(i));
                    }
                    dataList.add(columnNameList);
                    // 添加表格数据
                    for (int i = 0; i < suSheModel.getRowCount(); i++) {
                        java.util.List<Object> rowDataList = new ArrayList<>();
                        for (int j = 0; j < suSheModel.getColumnCount(); j++) {
                            rowDataList.add(suSheModel.getValueAt(i, j));
                        }
                        dataList.add(rowDataList);
                    }
                    // 使用EasyExcel导出数据到Excel文件
                    EasyExcel.write(new FileOutputStream(new File(filePath))).sheet("Sheet1").doWrite(dataList);
                    JOptionPane.showMessageDialog(DormitoryManagementSystem.this, "导出成功！");
                } catch (IOException ex) {
                    ex.printStackTrace();
                    JOptionPane.showMessageDialog(DormitoryManagementSystem.this, "导出失败！");
                }
            }
        });
        searchPanel.add(printButton);
        tablePanel = new JPanel();
        suSheTable = new JTable(suSheModel);
        JScrollPane scrollPane = new JScrollPane(suSheTable);


        buttonPanel = new JPanel();
        modifyButton = new JButton("修改");
        JButton addButton = new JButton("增加");
        deleteButton = new JButton("删除");
        modifyButton.addActionListener(new SuSheModifyButtonListener());
        deleteButton.addActionListener(new SuSheDeleteButtonListener());
        addButton.addActionListener(new SuSheAddButtonListener());
        buttonPanel.add(addButton);
        buttonPanel.add(modifyButton);
        buttonPanel.add(deleteButton);

        tablePanel.setLayout(new BorderLayout());
        tablePanel.add(searchPanel, BorderLayout.NORTH);
        tablePanel.add(scrollPane, BorderLayout.CENTER);
        tablePanel.add(buttonPanel, BorderLayout.SOUTH);

        // 获取数据库连接
        connection = JdbcConnection.getConnection();

        // 查询所有记录并显示在表格中
        executeQuery("SELECT * FROM dormitories",suSheModel);
        return tablePanel;
    }


    private boolean isTriggerEnabled() throws SQLException {
        // 查询触发器状态
        String sql = "SELECT TRIGGER_SCHEMA, TRIGGER_NAME, ACTION_TIMING, EVENT_MANIPULATION FROM INFORMATION_SCHEMA.TRIGGERS WHERE EVENT_OBJECT_TABLE = 'dormitories'";
        try (PreparedStatement statement = connection.prepareStatement(sql)) {
            boolean result = false;

            // 执行查询，并找到enable_trigger触发器是否存在
            result = statement.executeQuery().next();

            return result;
        }
    }

    private void toggleTriggerState() throws SQLException {
        // 根据当前触发器状态执行相应的动作
        if (isTriggerEnabled()) {
            disableTrigger();
        } else {
            enableTrigger();
        }
    }

    private void enableTrigger() throws SQLException {
        Connection connection = JdbcConnection.getConnection();
        PreparedStatement statement = connection.prepareStatement("DROP TRIGGER IF EXISTS my_trigger;");
        statement.executeUpdate();
        statement.close();
        connection.close();
    }

    private void disableTrigger() throws SQLException {
        Connection connection = JdbcConnection.getConnection();
        PreparedStatement statement = connection.prepareStatement("delimiter //\n" +
                "CREATE TRIGGER my_trigger\n" +
                "AFTER INSERT ON dormitories\n" +
                "FOR EACH ROW\n" +
                "BEGIN\n" +
                "    SET SESSION sql_select_limit = DEFAULT;\n" +
                "END //\n" +
                "delimiter ;\n");
        statement.executeUpdate();
        statement.close();
        connection.close();
    }

    // 执行查询语句并更新表格显示
    private void executeQuery(String query,DefaultTableModel defaultTableModel) {
        try {
            statement = connection.createStatement();
            resultSet = statement.executeQuery(query);

            // 清空表格模型的数据
            defaultTableModel.setRowCount(0);

//            // 获取结果集的元数据
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
//
//            // 添加表格的表头
//            Vector<String> columnNames = new Vector<>();
//            for (int i = 1; i <= columnCount; i++) {
//                columnNames.add(metaData.getColumnName(i));
//            }
//            defaultTableModel.setColumnIdentifiers(columnNames);

            // 添加结果集中的数据到表格模型
            while (resultSet.next()) {
                Vector<Object> rowData = new Vector<>();
                for (int i = 1; i <= columnCount; i++) {
                    rowData.add(resultSet.getObject(i));
                }
                defaultTableModel.addRow(rowData);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 搜索按钮的监听器
    private class SuSheSearchButtonListener implements ActionListener {
        @Override
        public void actionPerformed(ActionEvent e) {
            String building = suShebuildingField.getText();
            String room = suSheRoomField.getText();
            String query = "SELECT * FROM dormitories WHERE 1=1";
            // 根据楼号和房间号拼接查询语句
            if(building != null && !building.equals("")){
                query = query + " AND building_no='" + building + "'";
            }
            if(room != null && !room.equals("")){
                query = query + " AND room_no='" + room + "'";
            }


            executeQuery(query,suSheModel);
        }
    }

    private class StudentSearchButtonListener implements ActionListener {
        @Override
        public void actionPerformed(ActionEvent e) {
            String building = stuNoField.getText();
            String room = stuNameField.getText();
            String query = "SELECT * FROM students WHERE 1=1";
            // 根据楼号和房间号拼接查询语句
            if(building != null && !building.equals("")){
                query = query + " AND no='" + building + "'";
            }
            if(room != null && !room.equals("")){
                query = query + " AND name='" + room + "'";
            }


            executeQuery(query,stuModel);
        }
    }

    private class GoodSearchButtonListener implements ActionListener {
        @Override
        public void actionPerformed(ActionEvent e) {
            String building = goodBuildingField.getText();
            String room = goodNameField.getText();
            String query = "SELECT * FROM goods WHERE 1=1";
            // 根据楼号和房间号拼接查询语句
            if(building != null && !building.equals("")){
                query = query + " AND building_no='" + building + "'";
            }
            if(room != null && !room.equals("")){
                query = query + " AND goods_name='" + room + "'";
            }


            executeQuery(query,goodModel);
        }
    }

    private class AccidentSearchButtonListener implements ActionListener {
        @Override
        public void actionPerformed(ActionEvent e) {
            String building = accidentBuildingField.getText();
            String room = accidentRoomField.getText();
            String query = "SELECT * FROM accidents WHERE 1=1";
            // 根据楼号和房间号拼接查询语句
            if(building != null && !building.equals("")){
                query = query + " AND building_no='" + building + "'";
            }
            if(room != null && !room.equals("")){
                query = query + " AND room_no='" + room + "'";
            }


            executeQuery(query,accidentModel);
        }
    }

    // 修改按钮的监听器
    private class SuSheModifyButtonListener implements ActionListener {
        @Override
        public void actionPerformed(ActionEvent e) {
            int selectedRow = suSheTable.getSelectedRow();
            if (selectedRow >= 0) {
                // 获取选中行的id
                int id = (int) suSheModel.getValueAt(selectedRow, 0);
                String building = (String) suSheModel.getValueAt(selectedRow, 1);
                String room = (String) suSheModel.getValueAt(selectedRow, 2);
                int personCount = (int) suSheModel.getValueAt(selectedRow, 3);

                // 创建修改窗口
                JFrame modifyFrame = new JFrame("修改宿舍信息");
                modifyFrame.setLayout(new FlowLayout());
                modifyFrame.setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);
                modifyFrame.setSize(300, 200);

                // 添加修改窗口的组件
                JTextField buildingField = new JTextField(building, 15);
                JTextField roomField = new JTextField(room, 15);
                JTextField personCountField = new JTextField(String.valueOf(personCount), 10);

                JButton confirmButton = new JButton("确认");
                confirmButton.addActionListener(new ActionListener() {
                    @Override
                    public void actionPerformed(ActionEvent e) {
                        try {
                            // 更新数据库中的记录
                            PreparedStatement preparedStatement = connection.prepareStatement("UPDATE dormitories SET building_no=?, room_no=?, person_count=? WHERE id=?");
                            preparedStatement.setString(1, buildingField.getText());
                            preparedStatement.setString(2, roomField.getText());
                            preparedStatement.setInt(3, Integer.parseInt(personCountField.getText()));
                            preparedStatement.setInt(4, id);
                            preparedStatement.executeUpdate();

                            // 更新表格显示
                            executeQuery("SELECT * FROM dormitories",suSheModel);

                            // 关闭修改窗口
                            modifyFrame.dispose();
                        } catch (SQLException ex) {
                            ex.printStackTrace();
                        }
                    }
                });

                modifyFrame.add(new JLabel("楼号："));
                modifyFrame.add(buildingField);
                modifyFrame.add(new JLabel("房间号："));
                modifyFrame.add(roomField);
                modifyFrame.add(new JLabel("入住人数："));
                modifyFrame.add(personCountField);
                modifyFrame.add(confirmButton);

                modifyFrame.setVisible(true);
            }
        }
    }

    // 修改按钮的监听器
    private class SuSheAddButtonListener implements ActionListener {
        @Override
        public void actionPerformed(ActionEvent e) {
            // 创建修改窗口
            JFrame modifyFrame = new JFrame("增加宿舍信息");
            modifyFrame.setLayout(new FlowLayout());
            modifyFrame.setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);
            modifyFrame.setSize(300, 200);

            // 添加修改窗口的组件
            JTextField buildingField = new JTextField("", 15);
            JTextField roomField = new JTextField("", 15);
            JTextField personCountField = new JTextField("", 10);

            JButton confirmButton = new JButton("确认");
            confirmButton.addActionListener(new ActionListener() {
                @Override
                public void actionPerformed(ActionEvent e) {
                    try {
                        // 更新数据库中的记录
                        PreparedStatement preparedStatement = connection.prepareStatement("insert into dormitories values(null,?,?,?,'空置')");
                        preparedStatement.setString(1, buildingField.getText());
                        preparedStatement.setString(2, roomField.getText());
                        preparedStatement.setInt(3, Integer.parseInt(personCountField.getText()));
                        preparedStatement.executeUpdate();

                        // 更新表格显示
                        executeQuery("SELECT * FROM dormitories",suSheModel);

                        // 关闭修改窗口
                        modifyFrame.dispose();
                    } catch (SQLException ex) {
                        ex.printStackTrace();
                    }
                }
            });

            modifyFrame.add(new JLabel("楼号："));
            modifyFrame.add(buildingField);
            modifyFrame.add(new JLabel("房间号："));
            modifyFrame.add(roomField);
            modifyFrame.add(new JLabel("入住人数："));
            modifyFrame.add(personCountField);
            modifyFrame.add(confirmButton);

            modifyFrame.setVisible(true);
        }
    }

    // 修改按钮的监听器
    private class StudentAddButtonListener implements ActionListener {
        @Override
        public void actionPerformed(ActionEvent e) {
            // 创建修改窗口
            JFrame modifyFrame = new JFrame("增加学生信息");
            modifyFrame.setLayout(new FlowLayout());
            modifyFrame.setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);
            modifyFrame.setSize(300, 200);

            // 添加修改窗口的组件
            JTextField noField = new JTextField("", 15);
            JTextField nameField = new JTextField("", 15);
            JTextField sexField = new JTextField("", 10);
            JTextField ageField = new JTextField("", 10);
            JTextField graduateField = new JTextField("", 10);

            JButton confirmButton = new JButton("确认");
            confirmButton.addActionListener(new ActionListener() {
                @Override
                public void actionPerformed(ActionEvent e) {
                    try {
                        // 更新数据库中的记录
                        PreparedStatement preparedStatement = connection.prepareStatement("insert into students values(null,?,?,?,?,?)");
                        preparedStatement.setString(1, noField.getText());
                        preparedStatement.setString(2, nameField.getText());
                        preparedStatement.setString(3, sexField.getText());
                        preparedStatement.setInt(4, Integer.parseInt(ageField.getText()));
                        preparedStatement.setString(5, graduateField.getText());
                        preparedStatement.executeUpdate();

                        // 更新表格显示
                        executeQuery("SELECT * FROM students",stuModel);

                        // 关闭修改窗口
                        modifyFrame.dispose();
                    } catch (SQLException ex) {
                        ex.printStackTrace();
                    }
                }
            });

            modifyFrame.add(new JLabel("学号："));
            modifyFrame.add(noField);
            modifyFrame.add(new JLabel("姓名："));
            modifyFrame.add(nameField);
            modifyFrame.add(new JLabel("性别："));
            modifyFrame.add(sexField);
            modifyFrame.add(new JLabel("年龄："));
            modifyFrame.add(ageField);
            modifyFrame.add(new JLabel("是否毕业："));
            modifyFrame.add(graduateField);
            modifyFrame.add(confirmButton);

            modifyFrame.setVisible(true);
        }
    }

    // 修改按钮的监听器
    private class StuModifyButtonListener implements ActionListener {
        @Override
        public void actionPerformed(ActionEvent e) {
            int selectedRow = stuTable.getSelectedRow();
            if (selectedRow >= 0) {
                // 获取选中行的id
                int id = (int) stuModel.getValueAt(selectedRow, 0);
                String no = (String) stuModel.getValueAt(selectedRow, 1);
                String name = (String) stuModel.getValueAt(selectedRow, 2);
                String sex = (String) stuModel.getValueAt(selectedRow, 3);
                int age = (int)stuModel.getValueAt(selectedRow,4);
                String graduate = (String)stuModel.getValueAt(selectedRow,5);
                // 创建修改窗口
                JFrame modifyFrame = new JFrame("修改学生信息");
                modifyFrame.setLayout(new FlowLayout());
                modifyFrame.setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);
                modifyFrame.setSize(300, 200);

                // 添加修改窗口的组件
                JTextField noField = new JTextField(no, 15);
                JTextField nameField = new JTextField(name, 15);
                JTextField sexField = new JTextField(sex, 10);
                JTextField ageField = new JTextField(String.valueOf(age),15);
                JTextField graduateField = new JTextField(String.valueOf(graduate),15);
                JButton confirmButton = new JButton("确认");
                confirmButton.addActionListener(new ActionListener() {
                    @Override
                    public void actionPerformed(ActionEvent e) {
                        try {
                            // 更新数据库中的记录
                            PreparedStatement preparedStatement = connection.prepareStatement("UPDATE students SET no=?, name=?, sex=?,age=?,graduate=? WHERE id=?");
                            preparedStatement.setString(1, noField.getText());
                            preparedStatement.setString(2, nameField.getText());
                            preparedStatement.setString(3, sexField.getText());
                            preparedStatement.setInt(4, Integer.parseInt(ageField.getText()));
                            preparedStatement.setString(5, graduateField.getText());
                            preparedStatement.setInt(6, id);
                            preparedStatement.executeUpdate();

                            // 更新表格显示
                            executeQuery("SELECT * FROM students",stuModel);

                            // 关闭修改窗口
                            modifyFrame.dispose();
                        } catch (SQLException ex) {
                            ex.printStackTrace();
                        }
                    }
                });

                modifyFrame.add(new JLabel("学号："));
                modifyFrame.add(noField);
                modifyFrame.add(new JLabel("姓名："));
                modifyFrame.add(nameField);
                modifyFrame.add(new JLabel("性别："));
                modifyFrame.add(sexField);
                modifyFrame.add(new JLabel("年龄："));
                modifyFrame.add(ageField);
                modifyFrame.add(new JLabel("毕业："));
                modifyFrame.add(graduateField);
                modifyFrame.add(confirmButton);

                modifyFrame.setVisible(true);
            }
        }
    }

    private class AccidentModifyButtonListener implements ActionListener {
        @Override
        public void actionPerformed(ActionEvent e) {
            int selectedRow = accidentTable.getSelectedRow();
            if (selectedRow >= 0) {
                // 获取选中行的id
                int id = (int) accidentModel.getValueAt(selectedRow, 0);
                String no = (String) accidentModel.getValueAt(selectedRow, 1);
                String name = (String) accidentModel.getValueAt(selectedRow, 2);
                String sex = (String) accidentModel.getValueAt(selectedRow, 3);
                // 创建修改窗口
                JFrame modifyFrame = new JFrame("修改事故信息");
                modifyFrame.setLayout(new FlowLayout());
                modifyFrame.setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);
                modifyFrame.setSize(300, 200);

                // 添加修改窗口的组件
                JTextField noField = new JTextField(no, 15);
                JTextField nameField = new JTextField(name, 15);
                JTextField sexField = new JTextField(sex, 10);
                JButton confirmButton = new JButton("确认");
                confirmButton.addActionListener(new ActionListener() {
                    @Override
                    public void actionPerformed(ActionEvent e) {
                        try {
                            // 更新数据库中的记录
                            PreparedStatement preparedStatement = connection.prepareStatement("UPDATE accidents SET building_no=?, room_no=?, reason=? WHERE id=?");
                            preparedStatement.setString(1, noField.getText());
                            preparedStatement.setString(2, nameField.getText());
                            preparedStatement.setString(3, sexField.getText());
                            preparedStatement.setInt(4, id);
                            preparedStatement.executeUpdate();

                            // 更新表格显示
                            executeQuery("SELECT * FROM students",stuModel);

                            // 关闭修改窗口
                            modifyFrame.dispose();
                        } catch (SQLException ex) {
                            ex.printStackTrace();
                        }
                    }
                });

                modifyFrame.add(new JLabel("楼号："));
                modifyFrame.add(noField);
                modifyFrame.add(new JLabel("房号："));
                modifyFrame.add(nameField);
                modifyFrame.add(new JLabel("事故原因："));
                modifyFrame.add(sexField);
                modifyFrame.add(confirmButton);

                modifyFrame.setVisible(true);
            }
        }
    }

    // 删除按钮的监听器
    private class SuSheDeleteButtonListener implements ActionListener {
        @Override
        public void actionPerformed(ActionEvent e) {
            int selectedRow = suSheTable.getSelectedRow();
            if (selectedRow >= 0) {
                int id = (int) suSheModel.getValueAt(selectedRow, 0);

                int confirmResult = JOptionPane.showConfirmDialog(DormitoryManagementSystem.this, "确定要删除该记录吗？", "确认删除", JOptionPane.YES_NO_OPTION);
                if (confirmResult == JOptionPane.YES_OPTION) {
                    try {
                        // 删除数据库中的记录
                        statement.executeUpdate("DELETE FROM dormitories WHERE id=" + id);

                        // 更新表格显示
                        executeQuery("SELECT * FROM dormitories",suSheModel);

                        // 弹窗显示删除成功字段
                        JOptionPane.showMessageDialog(DormitoryManagementSystem.this, "删除成功");
                    } catch (SQLException ex) {
                        ex.printStackTrace();
                    }
                }
            }
        }
    }

    // 删除按钮的监听器
    private class StuDeleteButtonListener implements ActionListener {
        @Override
        public void actionPerformed(ActionEvent e) {
            int selectedRow = stuTable.getSelectedRow();
            if (selectedRow >= 0) {
                int id = (int) stuModel.getValueAt(selectedRow, 0);

                int confirmResult = JOptionPane.showConfirmDialog(DormitoryManagementSystem.this, "确定要删除该记录吗？", "确认删除", JOptionPane.YES_NO_OPTION);
                if (confirmResult == JOptionPane.YES_OPTION) {
                    try {
                        // 删除数据库中的记录
                        statement.executeUpdate("DELETE FROM students WHERE id=" + id);

                        // 更新表格显示
                        executeQuery("SELECT * FROM students",stuModel);

                        // 弹窗显示删除成功字段
                        JOptionPane.showMessageDialog(DormitoryManagementSystem.this, "删除成功");
                    } catch (SQLException ex) {
                        ex.printStackTrace();
                    }
                }
            }
        }
    }

    // 删除按钮的监听器
    private class AccidentDeleteButtonListener implements ActionListener {
        @Override
        public void actionPerformed(ActionEvent e) {
            int selectedRow = accidentTable.getSelectedRow();
            if (selectedRow >= 0) {
                int id = (int) accidentModel.getValueAt(selectedRow, 0);

                int confirmResult = JOptionPane.showConfirmDialog(DormitoryManagementSystem.this, "确定要删除该记录吗？", "确认删除", JOptionPane.YES_NO_OPTION);
                if (confirmResult == JOptionPane.YES_OPTION) {
                    try {
                        // 删除数据库中的记录
                        statement.executeUpdate("DELETE FROM accidents WHERE id=" + id);

                        // 更新表格显示
                        executeQuery("SELECT * FROM accidents",accidentModel);

                        // 弹窗显示删除成功字段
                        JOptionPane.showMessageDialog(DormitoryManagementSystem.this, "删除成功");
                    } catch (SQLException ex) {
                        ex.printStackTrace();
                    }
                }
            }
        }
    }

    // 删除按钮的监听器
    private class GoodDeleteButtonListener implements ActionListener {
        @Override
        public void actionPerformed(ActionEvent e) {
            int selectedRow = goodTable.getSelectedRow();
            if (selectedRow >= 0) {
                long id = (long) goodModel.getValueAt(selectedRow, 0);

                int confirmResult = JOptionPane.showConfirmDialog(DormitoryManagementSystem.this, "确定要删除该记录吗？", "确认删除", JOptionPane.YES_NO_OPTION);
                if (confirmResult == JOptionPane.YES_OPTION) {
                    try {
                        // 删除数据库中的记录
                        statement.executeUpdate("DELETE FROM goods WHERE id=" + id);

                        // 更新表格显示
                        executeQuery("SELECT * FROM goods",goodModel);

                        // 弹窗显示删除成功字段
                        JOptionPane.showMessageDialog(DormitoryManagementSystem.this, "删除成功");
                    } catch (SQLException ex) {
                        ex.printStackTrace();
                    }
                }
            }
        }
    }

    public static void main(String[] args) {
        SwingUtilities.invokeLater(new Runnable() {
            @Override
            public void run() {
                DormitoryManagementSystem system = new DormitoryManagementSystem();

            }
        });
    }
}
